package com.db;

import java.sql.*;

public class DBUtil {

	private static String dbFilePath =Thread.currentThread().getContextClassLoader().getResource("").getPath();
	//mysql
	private static final String DBDRIVER = "com.mysql.jdbc.Driver";
	private static final String DBURL = "jdbc:mysql://localhost:3306/weixin?useUnicode=true&characterEncoding=UTF-8";

	//sqllite
	//private static final String DBURL = "jdbc:sqlite:"+dbFilePath+"res/studentManager.db";
	//private static final String DBDRIVER = "org.sqlite.JDBC";
	//连接数据库的用户名
	private static final String DBUSER = "root";
	//连接数据库的密码
	private static final String DBPASS = "root123";


	Connection con;
	Statement stmt;
	ResultSet result;

	/**
	 * 获取连接
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public Connection getConnection() throws ClassNotFoundException, SQLException{

		if(con!=null){
			return con;
		}
		//表示数据库的连接对象
		Class.forName(DBDRIVER); //1、使用CLASS 类加载驱动程序
		con = DriverManager.getConnection(DBURL,DBUSER,DBPASS); //2、连接数据库
		//con = DriverManager.getConnection(DBURL); //2、连接数据库
		return con;

	}
	/**
	 * 插入或更新sql
	 * @param sql
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public int  insertOrUpdate(String sql) throws ClassNotFoundException, SQLException{
		getConnection();
		stmt = con.createStatement(); //Statement 接口需要通过Connection 接口进行实例化操作
		int i=stmt.executeUpdate(sql); //执行SQL 语句
		//con.close(); // 4、关闭数据库
		return i;

	}

	/**
	 * 删除
	 * @param sql
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public int  delete(String sql) throws ClassNotFoundException, SQLException{
		getConnection();
		stmt = con.createStatement(); //Statement 接口需要通过Connection 接口进行实例化操作
		int i=stmt.executeUpdate(sql); //执行SQL 语句
		//con.close(); // 4、关闭数据库
		return i;

	}

	/**
	 * 插入返回主键值
	 * @param sql
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public int  insert(String sql) throws ClassNotFoundException, SQLException{
		getConnection();
		int id=0;
		PreparedStatement stmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		stmt.executeUpdate();
		ResultSet rs = stmt.getGeneratedKeys();
		if (rs.next()) {
			id = rs.getInt(1);
		}
		stmt.close();
		return id;


	}
	/**
	 * 查询sql
	 * @param sql
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public ResultSet  query(String sql) throws ClassNotFoundException, SQLException{
		getConnection();
		stmt = con.createStatement(); //Statement 接口需要通过Connection 接口进行实例化操作
		result = stmt.executeQuery(sql); //执行SQL 语句，查询数据库
		return result;
	}

	public void close(){

		try {
			if(stmt!=null){
				stmt.close();
			}
			if(result!=null){
				result.close();
			}
			if(con!=null){
				con.close();
				con=null;
			}



		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				if(stmt!=null){
					stmt.close();
				}
				if(result!=null){
					result.close();
				}
				if(con!=null){
					con.close();
					con=null;
				}

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}


	}


}
